package com.mbc.rmt.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.mbc.common.dao.DAO;
import com.mbc.common.param.CriteriaRMParams;
import com.mbc.rmt.data.RemittanceCashReceivedDataList;
import com.mbc.rmt.entities.RemittanceCashReceived;

public class RemittanceCashReceivedArchiveDAOImpls extends
		DAO<RemittanceCashReceived> implements RemittanceCashReceivedArchiveDAO {

	public RemittanceCashReceivedArchiveDAOImpls(Connection conn) {
		this.conn = conn;
		this.table_name = "RemittanceCashReceivedArchive";
	}

	@Override
	public void create(RemittanceCashReceived entity) throws SQLException {
		query = "insert into "
				+ table_name
				+ " ( ReceivedId, ReceiptNo, CreatedDate, ReceivedDate, BranchId, IssuedBranchId, CreatedBy, PaidCurrencyId, RemitAmount, PaidAmount, ReceivedCurrenyId, Remark, Status, ModifiedDate, ModifiedBy, ReceivedAmount, RefundAmount, ExtraFees, PaidBy, AddCodeId, Sender, Receiver, NRC, Address, Rate, RateOperator, BatchId) values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setLong(i++, entity.getReceivedId());
		pstmt.setString(i++, entity.getReceiptNo());
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getCreatedDate()
				.getTime()));
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getReceivedDate()
				.getTime()));
		pstmt.setLong(i++, entity.getBranchId());
		pstmt.setLong(i++, entity.getIssuedBranchId());
		pstmt.setLong(i++, entity.getCreatedBy());
		pstmt.setLong(i++, entity.getPaidCurrencyId());
		pstmt.setDouble(i++, entity.getRemitAmount());
		pstmt.setDouble(i++, entity.getPaidAmount());
		pstmt.setLong(i++, entity.getReceivedCurrenyId());
		pstmt.setString(i++, entity.getRemark());
		pstmt.setByte(i++, entity.getStatus());
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getModifiedDate()
				.getTime()));
		pstmt.setLong(i++, entity.getModifiedBy());
		pstmt.setDouble(i++, entity.getReceivedAmount());
		pstmt.setDouble(i++, entity.getRefundAmount());
		pstmt.setDouble(i++, entity.getExtraFees());
		pstmt.setLong(i++, entity.getPaidBy());
		pstmt.setLong(i++, entity.getAddCodeId());
		pstmt.setString(i++, entity.getSender());
		pstmt.setString(i++, entity.getReceiver());
		pstmt.setString(i++, entity.getNrc());
		pstmt.setString(i++, entity.getAddress());
		pstmt.setDouble(i++, entity.getRate());
		pstmt.setString(i++, entity.getRateOperator());
		pstmt.setLong(i++, entity.getBatchId());
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void udpate(RemittanceCashReceived entity) throws SQLException {
		query = "update "
				+ table_name
				+ " set ReceivedDate = ?, BranchId = ?, IssuedBranchId = ?, PaidCurrencyId = ?, RemitAmount = ?, PaidAmount = ?, ReceivedCurrenyId = ?, Remark = ?, Status = ?, ModifiedDate = ?, ModifiedBy = ?, ReceivedAmount = ?, RefundAmount = ?, ExtraFees = ?, PaidBy = ?, AddCodeId = ?, Sender = ?, Receiver = ?, NRC = ?, Address = ?, Rate = ?, RateOperator = ? where ReceivedId = "
				+ entity.getReceivedId();
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getReceivedDate()
				.getTime()));
		pstmt.setLong(i++, entity.getBranchId());
		pstmt.setLong(i++, entity.getIssuedBranchId());
		pstmt.setLong(i++, entity.getPaidCurrencyId());
		pstmt.setDouble(i++, entity.getRemitAmount());
		pstmt.setDouble(i++, entity.getPaidAmount());
		pstmt.setLong(i++, entity.getReceivedCurrenyId());
		pstmt.setString(i++, entity.getRemark());
		pstmt.setByte(i++, entity.getStatus());
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getModifiedDate()
				.getTime()));
		pstmt.setLong(i++, entity.getModifiedBy());
		pstmt.setDouble(i++, entity.getReceivedAmount());
		pstmt.setDouble(i++, entity.getRefundAmount());
		pstmt.setDouble(i++, entity.getExtraFees());
		pstmt.setLong(i++, entity.getPaidBy());
		pstmt.setLong(i++, entity.getAddCodeId());
		pstmt.setString(i++, entity.getSender());
		pstmt.setString(i++, entity.getReceiver());
		pstmt.setString(i++, entity.getNrc());
		pstmt.setString(i++, entity.getAddress());
		pstmt.setDouble(i++, entity.getRate());
		pstmt.setString(i++, entity.getRateOperator());
		pstmt.setLong(i++, entity.getBatchId());
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void delete(long key) throws SQLException {
		query = getDeleteQuery() + " where ReceivedId = " + key;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public RemittanceCashReceived find(long key) throws SQLException {
		RemittanceCashReceived l_RemittanceCashReceivedArchive = null;
		query = "select a.*, b.Code, c.BankCode from " + table_name + " a"
				+ " inner join Addcode b on a.AddCodeId = b.AddCodeId"
				+ " left join Bank c on a.PaidBy = c.BankId"
				+ " where a.ReceivedId = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			l_RemittanceCashReceivedArchive = readRecord(rs);
			l_RemittanceCashReceivedArchive.setAddCode(rs.getString("Code"));
			l_RemittanceCashReceivedArchive.setBankCode(rs
					.getString("BankCode") == null ? "" : rs
					.getString("BankCode"));
		}
		rs.close();
		pstmt.close();
		return l_RemittanceCashReceivedArchive;
	}

	@Override
	public ArrayList<RemittanceCashReceived> findAll() throws SQLException {
		ArrayList<RemittanceCashReceived> l_RemittanceCashReceivedArchiveList = new ArrayList<RemittanceCashReceived>();
		query = getSelectQuery();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			l_RemittanceCashReceivedArchiveList.add(readRecord(rs));
		}
		rs.close();
		pstmt.close();
		return l_RemittanceCashReceivedArchiveList;
	}

	@Override
	protected RemittanceCashReceived readRecord(ResultSet rs)
			throws SQLException {
		RemittanceCashReceived l_entity = new RemittanceCashReceived();
		l_entity.setReceivedId(rs.getLong("ReceivedId"));
		l_entity.setReceiptNo(rs.getString("ReceiptNo"));
		l_entity.setCreatedDate(rs.getTimestamp("CreatedDate"));
		l_entity.setReceivedDate(rs.getTimestamp("ReceivedDate"));
		l_entity.setBranchId(rs.getLong("BranchId"));
		l_entity.setIssuedBranchId(rs.getLong("IssuedBranchId"));
		l_entity.setCreatedBy(rs.getLong("CreatedBy"));
		l_entity.setPaidCurrencyId(rs.getLong("PaidCurrencyId"));
		l_entity.setRemitAmount(rs.getDouble("RemitAmount"));
		l_entity.setPaidAmount(rs.getDouble("PaidAmount"));
		l_entity.setReceivedCurrenyId(rs.getLong("ReceivedCurrenyId"));
		l_entity.setRemark(rs.getString("Remark"));
		l_entity.setStatus(rs.getByte("Status"));
		l_entity.setModifiedDate(rs.getTimestamp("ModifiedDate"));
		l_entity.setModifiedBy(rs.getLong("ModifiedBy"));
		l_entity.setReceivedAmount(rs.getDouble("ReceivedAmount"));
		l_entity.setRefundAmount(rs.getDouble("RefundAmount"));
		l_entity.setExtraFees(rs.getDouble("ExtraFees"));
		l_entity.setPaidBy(rs.getLong("PaidBy"));
		l_entity.setAddCodeId(rs.getLong("AddCodeId"));
		l_entity.setSender(rs.getString("Sender"));
		l_entity.setReceiver(rs.getString("Receiver"));
		l_entity.setNrc(rs.getString("NRC"));
		l_entity.setAddress(rs.getString("Address"));
		l_entity.setRate(rs.getDouble("Rate"));
		l_entity.setRateOperator(rs.getString("RateOperator"));
		l_entity.setBatchId(rs.getLong("BatchId"));
		return l_entity;
	}

	@Override
	public RemittanceCashReceivedDataList findRmtCashReceived(CriteriaRMParams cri)
			throws SQLException {
		RemittanceCashReceivedDataList l_DataList = new RemittanceCashReceivedDataList();
		ArrayList<RemittanceCashReceived> l_entityList = new ArrayList<RemittanceCashReceived>();

		/*String l_cri = " and (b.Code like N'%" + cri.getKeyword() + "%'"
				+ " or d.Name like N'%" + cri.getKeyword() + "%'"
				+ " or a.Sender like N'%" + cri.getKeyword() + "%'"
				+ " or a.ReceiptNo like N'%" + cri.getKeyword() + "%'"
				+ " or a.Receiver like N'%" + cri.getKeyword() + "%'"
				+ " or a.NRC like N'%" + cri.getKeyword() + "%')"
				+ " and a.BranchId = " + cri.getBranchId();*/
		
		String l_cri = "";
		if(cri.getFieldname().equals("Code"))
			l_cri = "and (b.Code like N'%" + cri.getKeyword() + "%')";
		else if(cri.getFieldname().equals("Name"))
			l_cri = "and (d.Name like N'%" + cri.getKeyword() + "%')";
		else
			l_cri = "and (a." + cri.getFieldname() + " like N'%" + cri.getKeyword() + "%')";
		
		l_cri += "and a.BranchId = " + cri.getBranchId();

		query = "set rowcount "
				+ cri.getSize()
				+ " select * from (select row_number() over(order by a.ReceivedId desc) as [rowno], a.*, b.Code, c.BankCode, d.Name as BranchName from "
				+ table_name + " a"
				+ " inner join Addcode b on a.AddCodeId = b.AddCodeId"
				+ " inner join Branch d on a.IssuedBranchId = d.BranchId"
				+ " left join Bank c on a.PaidBy = c.BankId" + " where 1 = 1"
				+ l_cri + ") " + table_name + " where rowno > "
				+ cri.getIndex();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			RemittanceCashReceived l_entity = readRecord(rs);
			l_entity.setAddCode(rs.getString("Code"));
			l_entity.setBankCode(rs.getString("BankCode") == null ? "" : rs
					.getString("BankCode"));
			l_entity.setIssusedBranchName(rs.getString("BranchName"));
			l_entityList.add(l_entity);
		}
		rs.close();
		pstmt.close();
		l_DataList.setEntityList(l_entityList);
		query = "select count(a.ReceivedId) from " + table_name + " a"
				+ " inner join Addcode b on a.AddCodeId = b.AddCodeId"
				+ " inner join Branch d on a.IssuedBranchId = d.BranchId"
				+ " left join Bank c on a.PaidBy = c.BankId" + " where 1 = 1"
				+ l_cri;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}

	public RemittanceCashReceivedDataList findAllRmtCashReceived(
			CriteriaRMParams cri) throws SQLException {
		RemittanceCashReceivedDataList l_DataList = new RemittanceCashReceivedDataList();
		ArrayList<RemittanceCashReceived> l_entityList = new ArrayList<RemittanceCashReceived>();

		/*String l_cri = " and ReceivedId like N'%" + cri.getKeyword() + "%'"
				+ " or a.Sender like N'%" + cri.getKeyword() + "%'"
				+ " or a.ReceiptNo like N'%" + cri.getKeyword() + "%'"
				+ " or a.Receiver like N'%" + cri.getKeyword() + "%'"
				+ " or a.NRC like N'%" + cri.getKeyword() + "%'"
				+ " and a.BranchId = " + cri.getBranchId();*/
		
		
		String l_cri = "";
		l_cri = "and (a." + cri.getFieldname() + " like N'%" + cri.getKeyword() + "%')";
		
		l_cri += "and a.BranchId = " + cri.getBranchId();

		query = "set rowcount "
				+ cri.getSize()
				+ "select * from (select  ROW_NUMBER () over (order by ReceivedId desc)[rowno], * from (select "
				+ "a.ReceivedId,a.ReceiptNo,a.CreatedDate,a.ReceivedDate,a.BranchId,a.IssuedBranchId, "
				+ "a.CreatedBy,a.PaidCurrencyId,a.RemitAmount,a.PaidAmount,a.ReceivedCurrenyId,a.Remark, " 
				+ "a.ModifiedDate,a.ModifiedBy,a.ReceivedAmount,a.RefundAmount,a.ExtraFees,a.PaidBy,a.AddCodeId, "
				+ "a.Sender,a.Receiver,a.NRC,a.Address,a.Rate,a.RateOperator, b.Code, c.BankCode, d.Name as BranchName "
				+ "from RemittanceCashReceived a "
				+ "inner join Addcode b on a.AddCodeId = b.AddCodeId "
				+ "inner join Branch d on a.IssuedBranchId = d.BranchId "
				+ "left join Bank c on a.PaidBy = c.BankId where 1=1 " + l_cri
				+ "union " + "select " + "a.ReceivedId,a.ReceiptNo,a.CreatedDate,a.ReceivedDate,a.BranchId,a.IssuedBranchId, "
				+ "a.CreatedBy,a.PaidCurrencyId,a.RemitAmount,a.PaidAmount,a.ReceivedCurrenyId,a.Remark, "
				+ "a.ModifiedDate,a.ModifiedBy,a.ReceivedAmount,a.RefundAmount,a.ExtraFees,a.PaidBy,a.AddCodeId, "
				+ "a.Sender,a.Receiver,a.NRC,a.Address,a.Rate,a.RateOperator, b.Code, c.BankCode, d.Name as BranchName "
				+ "from RemittanceCashReceivedArchive a "
				+ "inner join AddCode b on b.AddCodeId=a.AddCodeId "
				+ "inner join Branch d on d.BranchId = a.IssuedBranchId "
				+ "left join Bank c on c.BankId = a.PaidBy " + "where 1=1 "
				+ l_cri + ") a) b where rowno > " + cri.getIndex();
		/* MBC End */
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			RemittanceCashReceived l_entity = readRecords(rs);
			l_entity.setAddCode(rs.getString("Code"));
			l_entity.setBankCode(rs.getString("BankCode") == null ? "" : rs
					.getString("BankCode"));
			l_entity.setIssusedBranchName(rs.getString("BranchName"));
			l_entityList.add(l_entity);
		}
		rs.close();
		pstmt.close();
		l_DataList.setEntityList(l_entityList);
		query = " select count(ReceivedId) "
				+ " from "
				+ " (select a.ReceivedId,a.ReceiptNo,a.CreatedDate,a.ReceivedDate,a.BranchId,a.IssuedBranchId, "
				+ "a.CreatedBy,a.PaidCurrencyId,a.RemitAmount,a.PaidAmount,a.ReceivedCurrenyId,a.Remark, " 
				+ "a.ModifiedDate,a.ModifiedBy,a.ReceivedAmount,a.RefundAmount,a.ExtraFees,a.PaidBy,a.AddCodeId, "
				+ "a.Sender,a.Receiver,a.NRC,a.Address,a.Rate,a.RateOperator, b.Code, c.BankCode, d.Name as BranchName "
				+ " from RemittanceCashReceived a inner join Addcode b on a.AddCodeId = b.AddCodeId "
				+ " inner join Branch d on a.IssuedBranchId = d.BranchId "
				+ " left join Bank c on a.PaidBy = c.BankId where 1=1 " + l_cri
				+ " union "
				+ " select a.ReceivedId,a.ReceiptNo,a.CreatedDate,a.ReceivedDate,a.BranchId,a.IssuedBranchId, "
				+ "a.CreatedBy,a.PaidCurrencyId,a.RemitAmount,a.PaidAmount,a.ReceivedCurrenyId,a.Remark, "
				+ "a.ModifiedDate,a.ModifiedBy,a.ReceivedAmount,a.RefundAmount,a.ExtraFees,a.PaidBy,a.AddCodeId, "
				+ "a.Sender,a.Receiver,a.NRC,a.Address,a.Rate,a.RateOperator, b.Code, c.BankCode, d.Name as BranchName  from RemittanceCashReceivedArchive a "
				+ " inner join AddCode b on b.AddCodeId=a.AddCodeId "
				+ " inner join Branch d on d.BranchId = a.IssuedBranchId "
				+ " left join Bank c on c.BankId = a.PaidBy " + " where 1=1  "
				+ l_cri + ") a";
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}

	private RemittanceCashReceived readRecords(ResultSet rs)
			throws SQLException {
		RemittanceCashReceived l_entity = new RemittanceCashReceived();
		l_entity.setReceivedId(rs.getLong("ReceivedId"));
		l_entity.setReceiptNo(rs.getString("ReceiptNo"));
		l_entity.setCreatedDate(rs.getTimestamp("CreatedDate"));
		l_entity.setReceivedDate(rs.getTimestamp("ReceivedDate"));
		l_entity.setBranchId(rs.getLong("BranchId"));
		l_entity.setIssuedBranchId(rs.getLong("IssuedBranchId"));
		l_entity.setCreatedBy(rs.getLong("CreatedBy"));
		l_entity.setPaidCurrencyId(rs.getLong("PaidCurrencyId"));
		l_entity.setRemitAmount(rs.getDouble("RemitAmount"));
		l_entity.setPaidAmount(rs.getDouble("PaidAmount"));
		l_entity.setReceivedCurrenyId(rs.getLong("ReceivedCurrenyId"));
		l_entity.setRemark(rs.getString("Remark"));
		l_entity.setModifiedDate(rs.getTimestamp("ModifiedDate"));
		l_entity.setModifiedBy(rs.getLong("ModifiedBy"));
		l_entity.setReceivedAmount(rs.getDouble("ReceivedAmount"));
		l_entity.setRefundAmount(rs.getDouble("RefundAmount"));
		l_entity.setExtraFees(rs.getDouble("ExtraFees"));
		l_entity.setPaidBy(rs.getLong("PaidBy"));
		l_entity.setAddCodeId(rs.getLong("AddCodeId"));
		l_entity.setSender(rs.getString("Sender"));
		l_entity.setReceiver(rs.getString("Receiver"));
		l_entity.setNrc(rs.getString("NRC"));
		l_entity.setAddress(rs.getString("Address"));
		l_entity.setRate(rs.getDouble("Rate"));
		l_entity.setRateOperator(rs.getString("RateOperator"));
		return l_entity;
	}

	@Override
	public List<RemittanceCashReceived> findByDateRange(Date fromDate,
			Date toDate, long branchId) throws SQLException {
		ArrayList<RemittanceCashReceived> l_RemittanceCashReceivedArchiveList = new ArrayList<RemittanceCashReceived>();
		query = "select a.*, b.Code from " + table_name + " a"
				+ " inner join Addcode b on a.AddCodeId = b.AddCodeId"
				+ " where a.ReceivedDate >= ? and a.ReceivedDate <= ?";
		if (branchId != 0)
			query += " and a.BranchId = " + branchId;
		pstmt = conn.prepareStatement(query);
		pstmt.setDate(1, new java.sql.Date(fromDate.getTime()));
		pstmt.setDate(2, new java.sql.Date(toDate.getTime()));
		rs = pstmt.executeQuery();
		while (rs.next()) {
			RemittanceCashReceived l_RemittanceCashReceivedArchive = readRecord(rs);
			l_RemittanceCashReceivedArchive.setAddCode(rs.getString("Code"));
			l_RemittanceCashReceivedArchiveList
					.add(l_RemittanceCashReceivedArchive);
		}
		rs.close();
		pstmt.close();
		return l_RemittanceCashReceivedArchiveList;
	}

	@Override
	public RemittanceCashReceivedDataList findRmtCashReceived(long batchId)
			throws SQLException {
		RemittanceCashReceivedDataList l_DataList = new RemittanceCashReceivedDataList();
		ArrayList<RemittanceCashReceived> l_entityList = new ArrayList<RemittanceCashReceived>();

		query = " select a.*, b.Code, c.BankCode, d.Name as BranchName from "
				+ table_name + " a"
				+ " inner join Addcode b on a.AddCodeId = b.AddCodeId"
				+ " inner join Branch d on a.IssuedBranchId = d.BranchId"
				+ " left join Bank c on a.PaidBy = c.BankId"
				+ " where a.batchid = " + batchId;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			RemittanceCashReceived l_entity = readRecord(rs);
			l_entity.setAddCode(rs.getString("Code"));
			l_entity.setBankCode(rs.getString("BankCode") == null ? "" : rs
					.getString("BankCode"));
			l_entity.setIssusedBranchName(rs.getString("BranchName"));
			l_entityList.add(l_entity);
		}
		rs.close();
		pstmt.close();
		l_DataList.setEntityList(l_entityList);
		return l_DataList;
	}

	@Override
	public List<RemittanceCashReceived> findConfirmAll(long branchid, long defaultbranchid) throws SQLException {
		ArrayList<RemittanceCashReceived> l_RemittanceCashReceivedArchiveList = new ArrayList<RemittanceCashReceived>();
		query = getSelectQuery() + " rca inner join AddCode ac on rca.AddCodeId = ac.AddCodeId WHERE IssuedBranchId = " + defaultbranchid + " AND rca.BranchId = " + branchid + " AND TStatus IS NULL";
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			RemittanceCashReceived l_entity = readRecord(rs);
			l_entity.setAddCode(rs.getString("Code") == null ? "" : rs.getString("Code"));
			l_RemittanceCashReceivedArchiveList.add(l_entity);
		}
		rs.close();
		pstmt.close();
		return l_RemittanceCashReceivedArchiveList;
	}
}
